Clone Method Example

This example uses the Clone method to create copies of a Recordset and then lets the user position the record pointer of each copy independently.

Sub CloneX()

   Dim dbsNorthwind As Database
   Dim arstProducts(1 To 3) As Recordset
   Dim intLoop As Integer
   Dim strMessage As String
   Dim strFind As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' If the following SQL statement will be used often, 
   ' creating a permanent QueryDef will result in better
   ' performance.
   Set arstProducts(1) = dbsNorthwind.OpenRecordset( _
      "SELECT ProductName FROM Products " & _
      "ORDER BY ProductName", dbOpenSnapshot)

   ' Create two clones of the original Recordset.
   Set arstProducts(2) = arstProducts(1).Clone
   Set arstProducts(3) = arstProducts(1).Clone

   Do While True

      ' Loop through the array so that on each pass, the 
      ' user is searching a different copy of the same 
      ' Recordset.
      For intLoop = 1 To 3

         ' Ask for search string while showing where the
         ' current record pointer is for each Recordset.
         strMessage = _
            "Recordsets from Products table:" & vbCr & _
            "  1 - Original - Record pointer at " & _
            arstProducts(1)!ProductName & vbCr & _
            "  2 - Clone - Record pointer at " & _
            arstProducts(2)!ProductName & vbCr & _
            "  3 - Clone - Record pointer at " & _
            arstProducts(3)!ProductName & vbCr & _
            "Enter search string for #" & intLoop & ":"
         strFind = Trim(InputBox(strMessage))
         If strFind = "" Then Exit Do

         ' Find the search string; if there's no match, jump
         ' to the last record.
         With arstProducts(intLoop)
            .FindFirst "ProductName >= '" & strFind & "'"
            If .NoMatch Then .MoveLast
         End With

      Next intLoop

   Loop

   arstProducts(1).Close
   arstProducts(2).Close
   arstProducts(3).Close
   dbsNorthwind.Close

End Sub